package ch.lab24.android.rtm.provider;

import java.util.HashMap;

import ch.lab24.android.rtm.RememberTheMilk;
import ch.lab24.android.rtm.provider.RTM.Notes;
import ch.lab24.android.rtm.provider.RTM.NotesColumns;
import ch.lab24.android.rtm.provider.RTM.Tasks;
import ch.lab24.android.rtm.provider.RTM.TasksColumns;
import android.app.Activity;
import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

public class RTMProvider extends ContentProvider {

    protected SQLiteOpenHelper mOpenHelper;
    protected SQLiteDatabase mDb;
    
    private static final int TASK_BASE = 0;
    private static final int TASK = TASK_BASE;
    private static final int TASK_FILTER = TASK_BASE + 1;
    private static final int TASK_FILTER_NOTCOMPLETED = TASK_BASE + 2;
    private static final int TASK_ID = TASK_BASE + 3;

    private static final int NOTE_BASE = 1000;
    private static final int NOTE = NOTE_BASE;
    private static final int NOTE_FILTER = NOTE_BASE + 1;
    private static final int NOTE_ID = NOTE_BASE + 2;
    private static final int TASK_NOTE = NOTE_BASE + 3;
    
    /** this is suitable for use by insert/update/delete/query and may be passed
     * as a method call parameter. Only insert/update/delete/query should call .clear() on it */
    private final ContentValues mValues = new ContentValues();
    private DatabaseUtils.InsertHelper mTaskInserter;
    private DatabaseUtils.InsertHelper mNoteInserter;
    private static final UriMatcher sURIMatcher = new UriMatcher(UriMatcher.NO_MATCH);    
    private static final HashMap<String, String> sTaskProjectionMap;
    private static final HashMap<String, String> sNoteProjectionMap;
	
	/**
	 * Helper class to interact with the database that stores the Tasks.
	 */
	private class DatabaseHelper extends SQLiteOpenHelper {
	    private static final String DATABASE_NAME = "rememberthemilk";
	    private static final int DATABASE_VERSION = 1;
	    private Context mContext;

	    
	    DatabaseHelper(Context context) {
	        super(context, DATABASE_NAME, null, DATABASE_VERSION);
	        mContext = context;
	    }

	    @Override
	    public void onCreate(SQLiteDatabase db) {
	        db.execSQL("CREATE TABLE " + RTM.Tasks.TABLE + " ("
	                + "_id INTEGER PRIMARY KEY, "
	                + RTM.Tasks.TASKID + " TEXT, "
	                + RTM.Tasks.NAME + " TEXT, "
	                + RTM.Tasks.DUE + " INTEGER, "
	                + RTM.Tasks.TAGS + " TEXT, "
	                + RTM.Tasks.HAS_DUE + " INTEGER, "
	                + RTM.Tasks.ADDED + " INTEGER, "
	                + RTM.Tasks.COMPLETED + " INTEGER, "
	                + RTM.Tasks.DELETED + " INTEGER, "
	                + RTM.Tasks.PRIORITY + " INTEGER, "
	                + RTM.Tasks.POSTPONED + " TEXT, "
	                + RTM.Tasks.ESTIMATE + " TEXT, "
	                + RTM.Tasks.URL + " TEXT, "
	                + RTM.Tasks.HAS_NOTES + " INTEGER, "
	                + RTM.Tasks.CREATED + " TEXT, "
	                + RTM.Tasks.MODIFIED + " INTEGER);");

	                db.execSQL("CREATE TABLE " + RTM.Notes.TABLE + " ("
	                + "_id INTEGER PRIMARY KEY, "
	                + RTM.Notes.NOTESID + " TEXT, "
	                + RTM.Notes.TASKID + " INTEGER REFERENCES " + RTM.Tasks.TABLE + "(_id), "
	                + RTM.Notes.TITLE + " TEXT, "
	                + RTM.Notes.TEXT + " TEXT, "
	                + RTM.Notes.CREATED + " TEXT, "
	                + RTM.Notes.MODIFIED + " INTEGER);");
	        
	        db.execSQL("CREATE INDEX notesTasksIndex ON " + RTM.Notes.TABLE + " (" + RTM.Notes.TASKID + ");");

	        db.execSQL("CREATE TRIGGER notesInsert INSERT ON " + RTM.Notes.TABLE +
	                " BEGIN " +
	                    " UPDATE " + RTM.Tasks.TABLE + " SET " + RTM.Tasks.HAS_NOTES + "=" +
	                    "1" + 
	                    " WHERE " + RTM.Tasks._ID + "=NEW." + RTM.Tasks.TASKID + ";" +
	                " END");
//	        db.execSQL("CREATE TRIGGER notesInsert INSERT ON " + Notes.TABLE +
//	                " BEGIN " +
//	                    " UPDATE " + Tasks.TABLE + " SET " + Tasks.HAS_NOTES + "=" +
//	                    "(SELECT COUNT(*) FROM "+ Notes.TABLE +" WHERE "+ Notes.TASKID + "=NEW." + Tasks.TASKID + ")" + 
//	                    " WHERE " + Tasks._ID + "=NEW." + Tasks.TASKID + ";" +
//	                " END");
	        
	        db.execSQL("CREATE TRIGGER notesDelete DELETE ON " + RTM.Notes.TABLE +
	                " BEGIN " +
	                    " UPDATE " + RTM.Tasks.TABLE + " SET " + RTM.Tasks.HAS_NOTES + "=" +
	                    "0" + 
	                    " WHERE " + RTM.Tasks._ID + "=OLD." + RTM.Notes.TASKID + ";" +
	                " END");
	        
	        // Delete the notes row when the people row is deleted
	        db.execSQL(""
	                + " CREATE TRIGGER tasksDeleteAndNotes DELETE ON " + RTM.Tasks.TABLE
	                + " BEGIN"
	                + "   DELETE FROM " + RTM.Notes.TABLE + " WHERE " + RTM.Notes.TASKID + "=OLD._id;"
	                + " END");
	        
	    }



	    @Override
	    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    	
	        Log.w(RememberTheMilk.LOG_TAG, "Upgrading database from version " + oldVersion + " to " +
	                newVersion + ", which will destroy all old data");

	        // remove LastSync if we have a new DB
	        final SharedPreferences settings = mContext.getSharedPreferences(RememberTheMilk.PREFERENCES_NAME, Activity.MODE_PRIVATE);    		
	    	SharedPreferences.Editor editor = settings.edit();
	        editor.putLong(RememberTheMilk.PREFERENCES_LAST_SYNC, 0);
	        editor.commit();
	        
	        db.execSQL("DROP TABLE IF EXISTS " + RTM.Tasks.TABLE);
	        db.execSQL("DROP TABLE IF EXISTS " + RTM.Notes.TABLE);
	        onCreate(db);
	    }
	    
        @Override
        public void onOpen(SQLiteDatabase db) {
            mTaskInserter = new DatabaseUtils.InsertHelper(db, Tasks.TABLE);
            mNoteInserter = new DatabaseUtils.InsertHelper(db, Notes.TABLE);
        }
	    
	}
	
    public SQLiteDatabase getDatabase() {
        if (mDb == null) mDb = mOpenHelper.getWritableDatabase();
        return mDb;
     }
	
    @Override
    public boolean onCreate() {
        mOpenHelper = new DatabaseHelper(getContext());
        
        return true;
    }

    @Override
    public final Cursor query(final Uri url, final String[] projection,
            final String selection, final String[] selectionArgs,
            final String sortOrder) {
        return queryInternal(url, projection, selection, selectionArgs, sortOrder);
    }


    private Cursor queryInternal(Uri url, String[] projectionIn,
            String selection, String[] selectionArgs, String sort) {

        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        Uri notificationUri = Tasks.CONTENT_URI;

        // Generate the body of the query
        int match = sURIMatcher.match(url);

        if (RememberTheMilk.LOGV) Log.v(RememberTheMilk.LOG_TAG, "RTMProvider.query: url=" + url + ", match is " + match);

        switch (match) {
	        case TASK_ID:
	            qb.appendWhere("_id=");
	            qb.appendWhere(url.getPathSegments().get(1));
	            // fall through
	        case TASK:
	            qb.setTables(Tasks.TABLE);
	            qb.setProjectionMap(sTaskProjectionMap);
	            break;	            
	        case TASK_FILTER_NOTCOMPLETED:
	        	// fall through
                qb.appendWhere(Tasks.COMPLETED + " IS NULL ");	        	
	            qb.setTables(Tasks.TABLE);
	            qb.setProjectionMap(sTaskProjectionMap);
	            break;	            
	        case NOTE_ID:
	            qb.appendWhere("_id=");
	            qb.appendWhere(url.getPathSegments().get(1));
	            // fall through
	        case NOTE:
	            qb.setTables(Notes.TABLE);
	            qb.setProjectionMap(sNoteProjectionMap);
	            break;
	        case TASK_NOTE:
                qb.appendWhere(Notes.TASKID + "="+ url.getPathSegments().get(1));
	            qb.setTables(Notes.TABLE);
	            qb.setProjectionMap(sNoteProjectionMap);
	            break;



            default:
                throw new IllegalArgumentException("Unknown URL " + url);
        }

        // run the query
        final SQLiteDatabase db = getDatabase();

        Cursor c = qb.query(db, projectionIn, selection, selectionArgs,
                null, null, sort);
        
        if ((c != null) && !isTemporary()) {
            c.setNotificationUri(getContext().getContentResolver(), notificationUri);
        }
        
        return c;
    }
    
    @Override
    public String getType(Uri url) {
        int match = sURIMatcher.match(url);
        switch (match) {
	        case TASK:
	            return Tasks.CONTENT_TYPE;
	        case TASK_ID:
	            return Tasks.CONTENT_ITEM_TYPE;
            default:
                throw new IllegalArgumentException("Unknown URL");
        }
    }

    @Override
    public final Uri insert(final Uri url, final ContentValues values) {
        Uri result;
        SQLiteDatabase db = getDatabase();
        db.beginTransaction();
        try {
            result = insertInternal(url, values);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        if (result != null) {
            getContext().getContentResolver()
                    .notifyChange(url, null /* observer */, false /* sync */);
        }
        return result;
    }
    
    public Uri insertInternal(Uri url, ContentValues initialValues) {
        Uri resultUri = null;
        long rowID;

        int match = sURIMatcher.match(url);
        switch (match) {
	        case TASK: 
	            mValues.clear();
	            mValues.putAll(initialValues);
	            rowID = mTaskInserter.insert(mValues);
                if (rowID > 0) 
                    resultUri = ContentUris.withAppendedId(Tasks.CONTENT_URI, rowID);
	            break;
	        case NOTE: 
	            mValues.clear();
	            mValues.putAll(initialValues);
	            rowID = mNoteInserter.insert(mValues);
                if (rowID > 0) 
                    resultUri = ContentUris.withAppendedId(Notes.CONTENT_URI, rowID);
	            break;	        
            default:
                throw new UnsupportedOperationException("Cannot insert into URL: " + url);
        }

        return resultUri;
    }

    @Override
    public final int delete(final Uri url, final String selection,
            final String[] selectionArgs) {
        int result;
        SQLiteDatabase db = getDatabase();
        db.beginTransaction();
        try {
            result = deleteInternal(url, selection, selectionArgs);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        if (result > 0) {
            getContext().getContentResolver()
                    .notifyChange(url, null /* observer */, false /* sync */);
        }
        return result;
    }
    
    public int deleteInternal(Uri url, String userWhere, String[] whereArgs) {

        final int matchedUriId = sURIMatcher.match(url);
        switch (matchedUriId) {
	        case TASK_ID:
	            return deleteFromTasks(url.getPathSegments().get(1), userWhere, whereArgs);
	        case TASK:
	            return deleteFromTasks(null, userWhere, whereArgs);	        	
            default:
                throw new UnsupportedOperationException("Cannot delete that URL: " + url);
        }

    }

    private int deleteFromTasks(String rowId, String where, String[] whereArgs) {
        final SQLiteDatabase db = getDatabase();
        where = addIdToWhereClause(rowId, where);

        // perform the actual delete
        return db.delete(Tasks.TABLE, where, whereArgs);
    }
    
    @Override
    public final int update(final Uri url, final ContentValues values,
            final String selection, final String[] selectionArgs) {

        int result;
        SQLiteDatabase db = mOpenHelper.getWritableDatabase();
        db.beginTransaction();
        try {
            result = updateInternal(url, values, selection, selectionArgs);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        if (result > 0) {
            getContext().getContentResolver()
                    .notifyChange(url, null /* observer */, false /* sync */);
        }
        return result;
    }

    public int updateInternal(Uri url, ContentValues values, String userWhere, String[] whereArgs) {
        final SQLiteDatabase db = getDatabase();
        String tableToChange;
        String changedItemId;
        final int matchedUriId = sURIMatcher.match(url);
        switch (matchedUriId) {
            case TASK_ID:
                mValues.clear();
                mValues.putAll(values);
                values = mValues;
                tableToChange = Tasks.TABLE;
                changedItemId = url.getPathSegments().get(1);
                break;
            case NOTE_ID:
                mValues.clear();
                mValues.putAll(values);
                values = mValues;
                tableToChange = Notes.TABLE;
                changedItemId = url.getPathSegments().get(1);
                break;
            default:
                throw new UnsupportedOperationException("Cannot update URL: " + url);
        }

        String where = addIdToWhereClause(changedItemId, userWhere);
        int numRowsUpdated = db.update(tableToChange, values, where, whereArgs);
        return numRowsUpdated;
    }

    private static String addIdToWhereClause(String id, String where) {
        if (id != null) {
            StringBuilder whereSb = new StringBuilder("_id=");
            whereSb.append(id);
            if (!TextUtils.isEmpty(where)) {
                whereSb.append(" AND (");
                whereSb.append(where);
                whereSb.append(')');
            }
            return whereSb.toString();
        } else {
            return where;
        }
    }

    static {
        // Contacts URI matching table
        UriMatcher matcher = sURIMatcher;
        matcher.addURI(RememberTheMilk.AUTHORITY, "tasks", TASK);
        matcher.addURI(RememberTheMilk.AUTHORITY, "tasks/#", TASK_ID);
        matcher.addURI(RememberTheMilk.AUTHORITY, "tasks/#/notes", TASK_NOTE);
        matcher.addURI(RememberTheMilk.AUTHORITY, "tasks/notcompleted", TASK_FILTER_NOTCOMPLETED);
        matcher.addURI(RememberTheMilk.AUTHORITY, "notes", NOTE);
        matcher.addURI(RememberTheMilk.AUTHORITY, "notes/#", NOTE_ID);

        HashMap<String, String> map;

        // Create the common task columns
        HashMap<String, String> taskColumns = new HashMap<String, String>();
        taskColumns.put(TasksColumns.TASKID, Tasks.TASKID);
        taskColumns.put(TasksColumns.NAME, Tasks.NAME);
        taskColumns.put(TasksColumns.DUE, Tasks.DUE);
        taskColumns.put(TasksColumns.TAGS, Tasks.TAGS);
        taskColumns.put(TasksColumns.HAS_DUE, Tasks.HAS_DUE);
        taskColumns.put(TasksColumns.ADDED, Tasks.ADDED);
        taskColumns.put(TasksColumns.COMPLETED, Tasks.COMPLETED);
        taskColumns.put(TasksColumns.DELETED, Tasks.DELETED);
        taskColumns.put(TasksColumns.PRIORITY, Tasks.PRIORITY);
        taskColumns.put(TasksColumns.POSTPONED, Tasks.POSTPONED);
        taskColumns.put(TasksColumns.ESTIMATE, Tasks.ESTIMATE);
        taskColumns.put(TasksColumns.URL, Tasks.URL);
        taskColumns.put(TasksColumns.HAS_NOTES, Tasks.HAS_NOTES);
        taskColumns.put(TasksColumns.CREATED, Tasks.CREATED);
        taskColumns.put(TasksColumns.MODIFIED, Tasks.MODIFIED);

        // Create the common task columns
        HashMap<String, String> noteColumns = new HashMap<String, String>();
        noteColumns.put(NotesColumns.NOTESID, Notes.NOTESID);
        noteColumns.put(NotesColumns.TASKID, Notes.TASKID);
        noteColumns.put(NotesColumns.TITLE, Notes.TITLE);
        noteColumns.put(NotesColumns.TEXT, Notes.TEXT);
        noteColumns.put(NotesColumns.CREATED, Notes.CREATED);
        noteColumns.put(NotesColumns.MODIFIED, Notes.MODIFIED);
        
        // Task projection map
        map = new HashMap<String, String>();
        map.put(Tasks._ID, Tasks.TABLE + "._id AS " + Tasks._ID);
        map.putAll(taskColumns);
        sTaskProjectionMap = map;

        // Note projection map
        map = new HashMap<String, String>();
        map.put(Notes._ID, Notes.TABLE + "._id AS " + Notes._ID);
        map.putAll(noteColumns);
        sNoteProjectionMap = map;
    
    }    
}
